﻿using System;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Ink;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using System.Data;
using System.Threading;
using System.Text;
using VAdvantage.Classes;
using System.ServiceModel;
using VAdvantage.Model;


using System.Runtime.CompilerServices;
using VAdvantage.DataService;
using System.Collections.Generic;
using VAdvantage.Utility;
using System.Data.SqlClient;
using VAdvantage.Logging;



namespace VAdvantage.DataBase
{
    public class DB
    {
        private static char QUOTE = '\'';

        public static AutoResetEvent m_autoResetEvent = new AutoResetEvent(false);

        private static Logging.VLogger log = new Logging.VLogger(typeof(DB).FullName);


        static string greeting = "";
        public static void Load(Object sql)
        {
            //var resetEvent = new ManualResetEvent(false);


            //resetEvent.WaitOne();
        }

        // [MethodImpl(MethodImplOptions.Synchronized)]
        public static DataSet ExecuteDataset(string sql, SqlParameter[] param, string trxName)
        {
            //if (param != null)
            //{
            //    StringBuilder sb = new StringBuilder(sql);
            //    for (int i = 0; i < param.Length; i++)
            //    {
            //        sb.Replace(param[i].ParameterName, param[i].Value.ToString());
            //    }
            //    sql = sb.ToString();
            //}
            DataSet ds = null;

            Exception error = null;

            bool fetching = true;
            var client = Utility.AEnv.GetDataServiceDBClient();
            try
            {
                client.GetDataXMLCompleted += ((sender, e) =>
                {
                    if (e.Error != null)
                    {
                        log.Severe(e.Error.ToString());
                    }
                    else if (e.ServiceError != null)
                    {
                        error = new Exception(e.ServiceError.Message);
                        log.Severe(e.ServiceError.ToString());
                    }
                    else
                    {
                        ds = new DataSet();
                        ds.FromXml(e.Result);
                    }
                    //m_autoResetEvent.Set();

                    fetching = false;
                });

                client.GetDataXMLAsync(sql.ToString(), param, trxName);
                client.CloseAsync();

                while (fetching)
                {
                    System.Threading.Thread.Sleep(1);
                }



                //m_autoResetEvent.WaitOne();
            }
            catch
            {
                client.CloseAsync();
                fetching = false;
                //m_autoResetEvent.Set();
            }
            if (error != null)
                throw error;
            return ds;
        }

        // [MethodImpl(MethodImplOptions.Synchronized)]


        public static int ExecuteQuery(string sql, SqlParameter[] param, string trxName, bool ignoreError)
        {
            var client = Utility.AEnv.GetDataServiceDBClient();
            int i = -1;

            //if (param != null)
            //{
            //    StringBuilder sb = new StringBuilder(sql);
            //    for (int k = 0; k < param.Length; k++)
            //    {
            //        sb.Replace(param[k].ParameterName, param[k].Value.ToString());
            //    }
            //    sql = sb.ToString();
            //}
            bool fetching = true;
            Exception error = null;
            try
            {
                client.SetDataXMLCompleted += ((sender, e) =>
                {
                    if (e.Error != null)
                    {
                    }
                    else if (e.ServiceError != null)
                    {
                        error = new Exception(e.ServiceError.Message);

                    }
                    else
                    {
                        i = int.Parse(e.Result);
                    }
                    // m_autoResetEvent.Set();
                    fetching = false;
                });

                client.SetDataXMLAsync(sql.ToString(), param, trxName);
                client.CloseAsync();
                //m_autoResetEvent.WaitOne();
                while (fetching)
                {
                    System.Threading.Thread.Sleep(1);
                }

            }
            catch
            {
                //m_autoResetEvent.Set();
                fetching = false;
            }
            if (error != null)
            {
                if (ignoreError)
                {
                    log.Log(Level.WARNING, trxName, error.Message);
                }
                else
                {
                    log.Log(Level.SEVERE, trxName, error);
                    log.SaveError("DBExecuteError", error);
                }
                return -1;
            }
            return i;
        }

        /// <summary>
        /// Multiple delete
        /// </summary>
        /// <param name="sql">  list<String> </param>
        /// <param name="param">null</param>
        /// <param name="trxName">trxname</param>
        /// <returns>return List of execute query result in list<Int></Int>
        /// 0 for no change
        /// 1 for deleted
        /// -1 errror
        /// </returns>
        //public static List<int> ExecuteMultipleDeleteQuery(List<string> sql, SqlParameter[] param, string trxName)
        //{
        //    var client = Utility.AEnv.GetDataServiceBasic2Client();
        //    List<int> ret = null;

        //    //if (param != null)
        //    //{
        //    //    StringBuilder sb = new StringBuilder(sql);
        //    //    for (int k = 0; k < param.Length; k++)
        //    //    {
        //    //        sb.Replace(param[k].ParameterName, param[k].Value.ToString());
        //    //    }
        //    //    sql = sb.ToString();
        //    //}
        //    bool fetching = true;
        //    Exception error = null;
        //    try
        //    {
        //        client.ExecuteMultipleDeleteQueriesCompleted+= ((sender, e) =>
        //        {
        //            if (e.Error != null)
        //            {
        //            }
        //            else if (e.error != null)
        //            {
        //                error = new Exception(e.error.Message);
        //            }
        //            else
        //            {
        //                ret = e.Result;
        //            }
        //            // m_autoResetEvent.Set();
        //            fetching = false;
        //        });

        //        client.ExecuteMultipleDeleteQueriesAsync(sql ,trxName);
        //        client.CloseAsync();
        //        //m_autoResetEvent.WaitOne();
        //        while (fetching)
        //        {
        //            System.Threading.Thread.Sleep(5);
        //        }
        //    }
        //    catch
        //    {
        //        //m_autoResetEvent.Set();
        //        fetching = false;
        //    }
        //    if (error != null)
        //    {
        //        throw error;
        //    }
        //    return ret;
        //}

        public static int ExecuteQuery(string sql, SqlParameter[] param, string trxName)
        {
            return ExecuteQuery(sql, param, trxName, false);
        }


        //[MethodImpl(MethodImplOptions.Synchronized)]
        public static int ExecuteLobQuery(string sql, SqlParameter[] param, string trxName)
        {
            var client = Utility.AEnv.GetDataServiceDBClient();
            int i = -1;
            Exception error = null;
            try
            {

                //System.ServiceModel.BasicHttpBinding binding = new System.ServiceModel.BasicHttpBinding();
                //binding.MaxReceivedMessageSize = 2147483647; // int's max size
                //binding.MaxBufferSize = 2147483647; // int's max size
                // System.ServiceModel.EndpointAddress address = new System.ServiceModel.EndpointAddress(new Uri(Application.Current.Host.Source, "../Upload.svc"));



                client.SetDataLobXMLCompleted += ((sender, e) =>
                {

                    if (e.Error != null)
                    {

                    }
                    else if (e.ServiceError != null)
                    {
                        error = new Exception(e.ServiceError.Message);
                    }



                    try
                    {
                        i = int.Parse(e.Result);
                    }
                    catch
                    {

                    }
                    m_autoResetEvent.Set();
                });


                //if (param != null)
                //{
                //    StringBuilder sb = new StringBuilder(sql);
                //    for (int k = 0; k < param.Length; k++)
                //    {
                //        sb.Replace(param[k].ParameterName, param[k].Value.ToString());
                //    }
                //    sql = sb.ToString();
                //}

                if (DatabaseType.IsOracle)
                {


                }

                client.SetDataLobXMLAsync(sql.ToString(), param, trxName);
                client.CloseAsync();
                m_autoResetEvent.WaitOne();

            }
            catch
            {
                m_autoResetEvent.Set();
            }
            if (error != null)
                throw error;

            return i;
        }


        public static int ExecuteQuery(string sql)
        {
            return ExecuteQuery(sql, null, null);
        }

        // [MethodImpl(MethodImplOptions.Synchronized)]
        public static DataReader ExecuteReader(string sql, SqlParameter[] param, string trxName)
        {

            //if (param != null)
            //{
            //    StringBuilder sb = new StringBuilder(sql);
            //    for (int i = 0; i < param.Length; i++)
            //    {
            //        sb.Replace(param[i].ParameterName, param[i].Value.ToString());
            //    }
            //    sql = sb.ToString();
            //}

            DataReader dr = null;
            var client = Utility.AEnv.GetDataServiceDBClient();
            bool fetching = true;

            Exception error = null;
            try
            {

                client.GetDataXMLCompleted += ((sender, e) =>
                {

                    if (e.Error != null)
                    {
                        log.Severe(e.Error.InnerException.ToString());
                    }

                    else if (e.ServiceError != null)
                    {
                        error = new Exception(e.ServiceError.Message);
                        log.Severe(e.ServiceError.ToString());
                    }
                    else
                    {
                        dr = new DataReader();
                        dr.SetData(e.Result);
                    }

                    fetching = false;
                    //m_autoResetEvent.Set();
                });
                client.GetDataXMLAsync(sql.ToString(), param, trxName);

                client.CloseAsync();

                while (fetching)
                {
                    System.Threading.Thread.Sleep(1);
                }


                //m_autoResetEvent.WaitOne();
            }
            catch (Exception e)
            {
                client.Abort();
                //m_autoResetEvent.Set();
                fetching = false;
            }
            if (error != null)
                throw error;

            return dr;
        }



        //[MethodImpl(MethodImplOptions.Synchronized)]
        //public static DataReader ExecuteReaderBytes(string sql)
        //{
        //    //if (param != null)
        //    //{
        //    //    StringBuilder sb = new StringBuilder(sql);
        //    //    for (int i = 0; i < param.Length; i++)
        //    //    {
        //    //        sb.Replace(param[i].ParameterName, param[i].Value.ToString());
        //    //    }
        //    //    sql = sb.ToString();
        //    //}

        //    DataReader dr = new DataReader();

        //    //    //System.ServiceModel.BasicHttpBinding binding = new System.ServiceModel.BasicHttpBinding();
        //    //    //binding.MaxReceivedMessageSize = 2147483647; // int's max size
        //    //    //binding.MaxBufferSize = 2147483647; // int's max size
        //    //    //System.ServiceModel.EndpointAddress address = new System.ServiceModel.EndpointAddress(new Uri(Application.Current.Host.Source, "../DataServices.svc"));
        //    var client = Utility.AEnv.GetDataServiceTcpClient(); ;


        //    try
        //    {
        //        client.GetDataXMLCompleted += ((sender, e) =>
        //        {
        //            try
        //            {
        //                //UnicodeEncoding d = new UnicodeEncoding();
        //                //dr.SetData(d.GetString(e.Result, 0, e.Result.Length));
        //                dr.SetData(e.Result);
        //            }
        //            catch
        //            {

        //            }
        //            m_autoResetEvent.Set();
        //        });
        //        client.GetDataXMLAsync(sql.ToString());
        //        client.CloseAsync();

        //        m_autoResetEvent.WaitOne();
        //    }
        //    catch (Exception e)
        //    {
        //        client.Abort();
        //        m_autoResetEvent.Set();
        //    }
        //    return dr;
        //}


        //  [MethodImpl(MethodImplOptions.Synchronized)]
        public static int GetSQLValue(String trxName, String sql, string string_param1)
        {

            sql = sql.Replace("@param1", "'" + string_param1 + "'");

            int retValue = -2;
            DataSet ds = new DataSet();

            var client = Utility.AEnv.GetDataServiceDBClient();
            bool fetching = true;

            Exception error = null;
            try
            {

                client.GetScalarCompleted += ((sender, e) =>
                {
                    if (e.Error != null)
                    {
                        // MessageBox.Show(e.Error.ToString()); 
                    }

                    else if (e.ServiceError != null)
                    {

                        error = new Exception(e.ServiceError.Message);

                    }
                    else
                    {
                        ds.FromXml(e.Result);
                        if (ds.Tables.Count>0 && ds.Tables[0].Rows.Count > 0)
                        {
                            retValue = Utility.Util.GetValueOfInt(ds.Tables[0].Rows[0][0]);
                        }
                        else
                        {
                            retValue = 0;
                        }
                    }

                    fetching = false;
                    //m_autoResetEvent.Set();
                });

                client.GetScalarAsync(sql, null, trxName);
                client.CloseAsync();

                while (fetching)
                {
                    System.Threading.Thread.Sleep(1);
                }


                //m_autoResetEvent.WaitOne();
            }
            catch
            {
                //  m_autoResetEvent.Set();
                fetching = false;
            }

            if (error != null)
                throw error;
            return retValue;
        }


        //[MethodImpl(MethodImplOptions.Synchronized)]
        public static int GetSQLValue(String trxName, String sql, int int_param1)
        {

            sql = sql.Replace("@param1", int_param1.ToString());

            int retValue = -1;
            DataSet ds = new DataSet();

            var client = Utility.AEnv.GetDataServiceDBClient();

            bool fetching = true;

            Exception error = null;
            try
            {
                client.GetScalarCompleted += ((sender, e) =>
                {
                    if (e.Error != null)
                    {
                        //MessageBox.Show(e.Error.ToString());
                    }
                    else if (e.ServiceError != null)
                    {
                        error = new Exception(e.ServiceError.Message);

                    }
                    else
                    {
                        ds.FromXml(e.Result);
                        if (ds.Tables.Count>0 && ds.Tables[0].Rows.Count > 0)
                        {
                            retValue = Util.GetValueOfInt(ds.Tables[0].Rows[0][0]);
                        }
                        else
                        {
                            retValue = 0;
                        }
                    }

                    fetching = false;
                    //m_autoResetEvent.Set();
                });

                client.GetScalarAsync(sql, null, trxName);

                client.CloseAsync();

                while (fetching)
                {
                    System.Threading.Thread.Sleep(1);
                }



                //                m_autoResetEvent.WaitOne();
            }
            catch
            {
                fetching = false;
                //m_autoResetEvent.Set();
            }
            if (error != null)
                throw error;
            return retValue;
        }

        public static int GetSQLValue(String trxName, String sql, int param1, String param2)
        {
            int retValue = -1;
            //PreparedStatement pstmt = null;
            string sqlQuery = sql;
            IDataReader dr = null;
            //try
            //{
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@param1", param1);
            param[1] = new SqlParameter("@param2", param2);

            dr = DB.ExecuteReader(sql, param, trxName);
            if (dr.Read())
            {
                retValue = Utility.Util.GetValueOfInt(dr[0].ToString());
            }
            else
            {
                // log.config("No Value " + sql + " - Param1=" + int_param1);
            }
            dr.Close();
            //}
            //catch (Exception e)
            //{
            //    if (dr != null)
            //    {
            //        dr.Close();
            //    }
            //    log.Log(Level.SEVERE, sql + " - Param1=" + param1 + " [" + trxName + "]", e);
            //    //ErrorLog.FillErrorLog("DB.GetSQLValue", sqlQuery, e.Message, VAdvantage.Framework.Message.MessageType.ERROR);
            //}
            return retValue;
        }

        public static int GetSQLValue(String trxName, String sql)
        {
            return GetSQLValue(trxName, sql, null);
        }

        public static String TO_STRING(String txt)
        {
            return TO_STRING(txt, 0);
        }   //  TO_STRING

        /**
         *	Package Strings for SQL command in quotes.
         *  <pre>
         *		-	include in ' (single quotes)
         *		-	replace ' with ''
         *  </pre>
         *  @param txt  String with text
         *  @param maxLength    Maximum Length of content or 0 to ignore
         *  @return escaped string for insert statement (NULL if null)
         */
        public static String TO_STRING(String txt, int maxLength)
        {
            if (string.IsNullOrEmpty(txt))
                return "NULL";

            //  Length
            String text = txt;
            if (maxLength != 0 && text.Length > maxLength)
                text = txt.Substring(0, maxLength);

            //  copy characters		(we need to look through anyway)
            StringBuilder outt = new StringBuilder();
            outt.Append(QUOTE);		//	'
            for (int i = 0; i < text.Length; i++)
            {
                char c = text[i];
                if (c == QUOTE)
                    outt.Append("''");
                else
                    outt.Append(c);
            }
            outt.Append(QUOTE);		//	'
            //
            return outt.ToString();
        }	//	TO_STRING

        public static String TO_DATE(DateTime? day)
        {
            return TO_DATE(day, true);
        }

        public static String TO_CHAR(String columnName, int displayType, String AD_Language)
        {
            StringBuilder retValue = new StringBuilder("TRIM(TO_CHAR(");
            if (DatabaseType.IsOracle)
            {
                retValue.Append(columnName);

                //  Numbers
                if (DisplayType.IsNumeric(displayType))
                {
                    if (displayType == DisplayType.Amount)
                        retValue.Append(",'9G999G990D00'");
                    else
                        retValue.Append(",'TM9'");
                    //  TO_CHAR(GrandTotal,'9G999G990D00','NLS_NUMERIC_CHARACTERS='',.''')
                    //if (!Language.isDecimalPoint(AD_Language))      //  reversed
                    //    retValue.append(",'NLS_NUMERIC_CHARACTERS='',.'''");
                }
                else if (DisplayType.IsDate(displayType))
                {
                    retValue.Append(",'")
                        .Append("yyyy-MM-dd")
                        .Append("'");
                }
                retValue.Append("))");
                //
                return retValue.ToString();
            }
            else if (DatabaseType.IsPostgre)
            {
                //StringBuilder retValue = new StringBuilder("TRIM(TO_CHAR(");
                retValue.Append(columnName);

                //  Numbers
                if (DisplayType.IsNumeric(displayType))
                {
                    if (displayType == DisplayType.Amount)
                        retValue.Append(",'9G999G990D00'");
                    //jz no TM9 format to EDB 
                    //else
                    //	retValue.append(",'TM9'");
                    //  TO_CHAR(GrandTotal,'9G999G990D00','NLS_NUMERIC_CHARACTERS='',.''')
                    //if (!Language.isDecimalPoint(AD_Language))      //jz EDB not supported  reversed
                    //	retValue.append(",'NLS_NUMERIC_CHARACTERS='',.'''");
                }
                else if (DisplayType.IsDate(displayType))
                {
                    retValue.Append(",'")
                        .Append("yyyy-MM-dd")
                        .Append("'");
                }
                retValue.Append("))");
                //
                return retValue.ToString();
            }

            return retValue.ToString();
        }   //  TO_CHAR

        public static String TO_DATE(DateTime? time, bool dayOnly)
        {
            if (!dayOnly)
            {
                time = GlobalVariable.SetDateTimeUTC(time);
            }

            StringBuilder dateString = new StringBuilder("");
            string myDate = "";
            if (DatabaseType.IsOracle)
            {
                if (time == null)
                {
                    if (dayOnly)
                        return "TRUNC(SysDate)";
                    return "SysDate";
                }

                dateString = new StringBuilder("TO_DATE('");
                //  YYYY-MM-DD HH24:MI:SS.mmmm  JDBC Timestamp format
                //String myDate = time.ToString("yyyy-mm-dd");
                //myDate = time.ToString("yyyy-MM-dd HH:mm:ss");
                myDate = time.Value.ToString();//"yyyy-MM-dd");
                if (dayOnly)
                {
                    myDate = time.Value.ToString("yyyy-MM-dd");
                    dateString.Append(myDate);
                    dateString.Append("','YYYY-MM-DD')");
                }
                else
                {
                    myDate = time.Value.ToString("yyyy-MM-dd HH:mm:ss");
                    dateString.Append(myDate);	//	cut off miliseconds
                    dateString.Append("','YYYY-MM-DD HH24:MI:SS')");
                }
            }
            else if (DatabaseType.IsPostgre)
            {
                if (time == null)
                {
                    if (dayOnly)
                        return "TRUNC(SysDate)";
                    return "SysDate";
                }

                dateString = new StringBuilder("TO_DATE('");
                //  YYYY-MM-DD HH24:MI:SS.mmmm  JDBC Timestamp format
                if (dayOnly)
                {
                    myDate = time.Value.ToString("yyyy-MM-dd");
                    dateString.Append(myDate);
                    dateString.Append("','YYYY-MM-DD')");
                }
                else
                {
                    myDate = time.Value.ToString("yyyy-MM-dd HH:mm:ss");
                    dateString.Append(myDate);	//	cut off miliseconds
                    dateString.Append("','YYYY-MM-DD HH24:MI:SS')");
                }
            }
            else if (DatabaseType.IsMSSql)
            {
                if (time == null)
                {
                    if (dayOnly)
                        return "CAST(STR(YEAR(Getdate()))+'-'+STR(Month(Getdate()))+'-'+STR(Day(Getdate())) AS DATETIME)";
                    return "getdate()";
                }

                dateString = new StringBuilder("CAST('");
                //  YYYY-MM-DD HH24:MI:SS.mmmm  JDBC Timestamp format
                myDate = time.ToString();
                if (dayOnly)
                {
                    dateString.Append(myDate.Substring(0, 10));
                    dateString.Append("' AS DATETIME)");
                }
                else
                {
                    dateString.Append(myDate.Substring(0, myDate.IndexOf(".")));	//	cut off miliseconds
                    dateString.Append("' AS DATETIME)");
                }
            }
            return dateString.ToString();
        }

        public static bool UseMigratedConnection { get; set; }

        public static IDataReader ExecuteReader(string sql, SqlParameter[] param)
        {
            return ExecuteReader(sql, param, null);
        }

        public static IDataReader ExecuteReader(string sql)
        {
            return ExecuteReader(sql, null, null);
        }

        public static DataSet ExecuteDataset(string sql, SqlParameter[] param)
        {
            return ExecuteDataset(sql, param, null);
        }

        //[MethodImpl(MethodImplOptions.Synchronized)]
        public static object ExecuteScalar(String sql, SqlParameter[] param, string trxName)
        {
            object retValue = null;
            DataSet ds = new DataSet();

            var client = Utility.AEnv.GetDataServiceDBClient();
            bool fetching = true;
            Exception error = null;
            try
            {
                client.GetScalarCompleted += ((sender, e) =>
                {
                    try
                    {
                        if (e.Error != null)
                        {
                           // MessageBox.Show(e.Error.ToString());
                            retValue = null;
                        }
                        else if (
                            e.ServiceError != null)
                        {

                            error = new Exception(e.ServiceError.Message);
                        }
                        else
                        {
                            ds.FromXml(e.Result);
                            if (ds.Tables.Count>0 && ds.Tables[0].Rows.Count > 0)
                            {
                                retValue = ds.Tables[0].Rows[0][0];
                                if (retValue == DBNull.Value)
                                {
                                    retValue = null;
                                }
                            }
                        }
                    }
                    catch
                    {

                    }
                    fetching = false;
                    //m_autoResetEvent.Set();
                });
                client.GetScalarAsync(sql, param, trxName);
                client.CloseAsync();


                while (fetching)
                {
                    System.Threading.Thread.Sleep(1);

                }


                //m_autoResetEvent.WaitOne();
            }
            catch
            {
                fetching = false;
                client.Abort();
                //m_autoResetEvent.Set();

            }
            if (error != null)
                throw error;
            return retValue;
        }

       public static object ExecuteScalar(string sql)
        {
            return ExecuteScalar(sql, null, null);
        }


        //Normal DataSet Getter


        [MethodImpl(MethodImplOptions.Synchronized)]
        public static DataSetData ExecuteDataObject(string sql)
        {
            DataSetData dataObject = null;

            var client = Utility.AEnv.GetDataServiceClient();

            client.GetDataObjectCompleted += (sender, e) =>
            {
                dataObject = e.Result;
                m_autoResetEvent.Set();
            };

            client.GetDataObjectAsync(sql, 0, 0);
            m_autoResetEvent.WaitOne();
            return dataObject;
        }


        [MethodImpl(MethodImplOptions.Synchronized)]
        public static DataSetData ExecuteDataObject(string sql, SqlParameter[] param)
        {
            //if (param != null)
            //{
            //    StringBuilder sb = new StringBuilder(sql);
            //    for (int i = 0; i < param.Length; i++)
            //    {
            //        sb.Replace(param[i].ParameterName, param[i].Value.ToString());
            //    }
            //    sql = sb.ToString();
            //}

            DataSetData dataObject = null;

            var client = Utility.AEnv.GetDataServiceClient();

            try
            {

                client.GetDataObjectCompleted += (sender, e) =>
                {
                    dataObject = e.Result;
                    m_autoResetEvent.Set();
                };

                client.GetDataObjectAsync(sql, 0, 0);
                client.CloseAsync();
                m_autoResetEvent.WaitOne();
            }
            catch
            {
                m_autoResetEvent.Set();
            }
            return dataObject;
        }

        /// <summary>
        /// Get next number for Key column = 0 is Error.
        /// </summary>
        /// <param name="AD_Client_ID">client</param>
        /// <param name="TableName">table name</param>
        /// <param name="trxName">optional Transaction Name</param>
        /// <returns>next no</returns>
        public static int GetNextID(int AD_Client_ID, String TableName, String trxName)
        {
            //if ((trxName == null || trxName.Length() == 0) && isRemoteObjects())
            //{
            //    Server server = CConnection.get().getServer();
            //    try
            //    {
            //        if (server != null)
            //        {	//	See ServerBean
            //            int id = server.getNextID(AD_Client_ID, TableName, null);
            //            //log.finest("server => " + id);
            //            if (id < 0)
            //                throw new DBException("No NextID");
            //            return id;
            //        }
            //        log.log(Level.SEVERE, "AppsServer not found - " + TableName);
            //    }
            //    catch (RemoteException ex)
            //    {
            //        log.log(Level.SEVERE, "AppsServer error", ex);
            //    }
            //    //	Try locally
            //}
            ////jz let trxName = null so that the previous workflow node wouldn't block startnext  to invoke this method
            //   in the case trxName rollback, it's OK to keep the pumpup sequence.
            //TODO
            //if (isDB2())
            //    trxName = null;	//	tries 3 times
            int id = MSequence.GetNextID(AD_Client_ID, TableName, trxName);	//	tries 3 times
            //	if (id <= 0)
            //		throw new DBException("No NextID (" + id + ")");
            return id;
        }


        internal static object GetConnection()
        {
            throw new NotImplementedException();
        }

        //internal static bool IsConnected()
        //{
        //    return true;
        //}

        /// <summary> 
        /// Do we have an Oracle DB ?
        /// </summary>
        /// <returns>true if connected to Oracle</returns>
        public static Boolean IsOracle()
        {
            return DatabaseType.IsOracle;
        }
        public static KeyNamePair[] GetKeyNamePairs(string sql, bool optional)
        {
            /// <summary>
            /// Get Array of Key Name Pairs
            /// </summary>
            /// <param name="sql">select with id / name as first / second column</param>
            /// <param name="optional">if true (-1,"") is added </param>
            /// <returns>array of key name pairs</returns>

            DataTable dt = null;
            IDataReader idr = null;
            List<KeyNamePair> list = new List<KeyNamePair>();
            if (optional)
            {
                list.Add(new KeyNamePair(-1, ""));
            }
            try
            {
                idr = DB.ExecuteReader(sql, null, null);
                dt = new DataTable();
                dt.Load(idr);
                idr.Close();
                foreach (DataRow dr in dt.Rows)// while (rs.next())
                {
                    list.Add(new KeyNamePair(Util.GetValueOfInt(dr[0]), dr[1].ToString()));//.getString(2)));
                }
            }
            catch (Exception e)
            {
                if (idr != null)
                {
                    idr.Close();
                }
                log.Log(Level.SEVERE, sql, e);
            }
            KeyNamePair[] retValue = new KeyNamePair[list.Count];
            retValue = list.ToArray();
            return retValue;
        }

        public static Decimal? GetSQLValueBD(String trxName, String sql, int int_param1)
        {
            Decimal? retValue = null;
            SqlParameter[] param = new SqlParameter[1];
            IDataReader idr = null;
            try
            {
                //pstmt = prepareStatement(sql, trxName);
                //pstmt.setInt(1, int_param1);
                param[0] = new SqlParameter("@param1", int_param1);
                idr = DB.ExecuteReader(sql, param, trxName);
                if (idr.Read())
                {
                    retValue = Util.GetValueOfDecimal(idr[0]);// rs.getBigDecimal(1);
                }
                else
                    log.Info("No Value " + sql + " - Param1=" + int_param1);
                idr.Close();
            }
            catch (Exception e)
            {
                log.Log(Level.SEVERE, sql + " - Param1=" + int_param1 + " [" + trxName + "]", e);
            }

            return retValue;
        }

        public static void ExecuteMultipleQueries(List<String> queries, String trxName)
        {

            var client = AEnv.GetDataServiceBasic2Client();

            Exception error = null;
            bool isExecuting = true;

            client.ExecuteMultipleQueriesCompleted += (s, e) =>
                {
                    if (e.Error != null)
                    {
                        error = e.Error;
                    }

                    else if (e.ServiceError != null)
                    {
                        List<String> ret = e.Result;
                        foreach (String str in ret)
                        {
                            log.Log(Level.SEVERE, "Model - Not Updated:=" + str);
                        }
                        error = new Exception(e.ServiceError.Message);
                    }

                    else
                    {
                        log.Fine("OK");
                    }
                    isExecuting = false;
                };
            client.ExecuteMultipleQueriesAsync(queries, trxName);
            client.CloseAsync();

            while (isExecuting)
            {
                System.Threading.Thread.Sleep(1);
            }

            if (error != null)
            {
                throw error;
            }
        }



        /// <summary>
        ///Commit - commit on RW connection.
        //Is not required as RW connection is AutoCommit (exception: with transaction)
        /// </summary>
        /// <param name="throwException">if true, re-throws exception</param>
        /// <param name="trxName">trx name</param>
        /// <returns>true if not needed or success</returns>
        public static bool Commit(bool throwException, String trxName)
        {
            try
            {
              //  Connection conn = null;
                // IDbConnection conn = null;
                Trx trx = trxName == null ? null : Trx.Get(trxName, true);
                if (trx != null)
                {
                  //  conn = trx.GetConnection();
                    trx.Commit();
                }
                else
                {
                   // conn = DataBase.DB.GetConnection();
                }
                //	if (!conn.getAutoCommit())
                //conn.commit();
            }
            catch (Exception e)
            {
                log.Log(Level.SEVERE, "[" + trxName + "]", e);
                if (throwException)
                {
                    throw e;
                }
                return false;
            }
            return true;
        }	//	commit



        /// <summary>
        ///	Rollback - rollback on RW connection.
        //Is has no effect as RW connection is AutoCommit (exception: with transaction)
        /// </summary>
        /// <param name="throwException">if true, re-throws exception</param>
        /// <param name="trxName">transaction name</param>
        /// <returns> true if not needed or success</returns>
        public static bool Rollback(bool throwException, String trxName)
        {
            try
            {
                //Connection conn = null;
               // IDbConnection conn = null;
                Trx trx = trxName == null ? null : Trx.Get(trxName, true);
                if (trx != null)
                {
                ///    conn = trx.GetConnection();
                    trx.Rollback();
                }
                else
                {
                   //// conn = DataBase.DB.GetConnection();
                }
                //	if (!conn.getAutoCommit())

            }
            catch (Exception e)
            {
                log.Log(Level.SEVERE, "[" + trxName + "]", e);
                if (throwException)
                {
                    throw e;
                }
                return false;
            }
            return true;
        }	//	commit

        /// <summary>
        /// return sql scalar value list<object>
        /// </summary>
        /// <param name="trxName">tranaction name</param>
        /// <param name="sql"> list<string> sql queries</string> </param>
        /// <returns>List of Object {may have (int,string,datetime or decimal}  </returns>
        public static Object[] GetSQLScalarValueObjectEx(String trxName, string[]  sqlArr)
        {
            Object[] retValue = null;

            
              Exception error = null;
            bool isExecuting = true;

             var client= AEnv.GetDataServiceDBClient();

            client.GetScalarObjectArrayCompleted +=(s,e)=>
                {
                    if(e.Error!= null)
                    {
                        error =e.Error;
                    }
                    else if(e.ServiceError != null)
                    {
                        error = new Exception(e.ServiceError.Message);
                    }
                    else 
                    {
                        retValue = e.Result;
                    }
                    isExecuting = false;

                };

            client.GetScalarObjectArrayAsync(sqlArr,trxName);
            client.CloseAsync();

            while (isExecuting)
            {
                System.Threading.Thread.Sleep(5);
            }
            
            if(error != null)
            {
                throw error;
            }

            return retValue;
        }

        /// <summary>
        /// Checks if database is available to be connected
        /// </summary>
        /// <returns></returns>
        public static bool IsConnected()
        {
            //Exception ex = VConnection.Get().Connect();
            //if (ex == null) return true;
            return true;
            //VConnection vconn = VConnection.Get();
            //IDbConnection conn = Ini.GetConnection(vconn);
            //try
            //{
            //    conn.Open();
            //    return true;
            //}
            //catch
            //{
            //    return false;
            //}
            //finally
            //{
            //    if (conn != null)
            //    {
            //        conn.Close();
            //    }
            //}

        }


        public static object GetScalarObject(string dSet)
        {
            object retValue = null;
            DataSet ds = new DataSet();
            ds.FromXml(dSet);
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                retValue = ds.Tables[0].Rows[0][0];
                if (retValue == DBNull.Value)
                {
                    retValue = null;
                }
            }
            return retValue;
        }
    }

    
}
